/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.bt.dmdb.controller.impl;

/**
 *
 * @author sarthak
 */
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import javax.annotation.Resource;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.LocalDateTime;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.servlet.ModelAndView;

import com.bt.dmdb.model.BusinessNature;
import com.bt.dmdb.model.Contact;
import com.bt.dmdb.model.CustomerDetails;
import com.bt.dmdb.model.CustomerDocument;
import com.bt.dmdb.model.CustomerQuestion;
import com.bt.dmdb.model.Document;
import com.bt.dmdb.model.DocumentDto;
import com.bt.dmdb.model.EagPassiveIncome;
import com.bt.dmdb.model.Locations;
import com.bt.dmdb.model.LoginUser;
import com.bt.dmdb.model.Market;
import com.bt.dmdb.model.MemberStatus;
import com.bt.dmdb.model.Question;
import com.bt.dmdb.model.QuestionDTO;
import com.bt.dmdb.model.QuestionRDTO;
import com.bt.dmdb.model.Regulators;
import com.bt.dmdb.model.RestrictedDistributor;
import com.bt.dmdb.model.SourceIncome;
import com.bt.dmdb.model.StocksDistribution;
import com.bt.dmdb.service.QuestionService;
import com.bt.dmdb.service.UsersService;

@Controller
public class LinkNavigation {

    @Resource
    private UsersService usersService;
    
    @Resource
    private QuestionService questionService;

    public QuestionService getQuestionService() {
		return questionService;
	}

	public void setQuestionService(QuestionService questionService) {
		this.questionService = questionService;
	}

	public UsersService getUsersService() {
        return usersService;
    }

    public void setUsersService(UsersService usersService) {
        this.usersService = usersService;
    }

    /*
     * Forwarding  to login jsp
     */
    @RequestMapping(value = "/", method = RequestMethod.GET)
    public ModelAndView homePage() {
        return new ModelAndView("login");
    }

    /*
     * Forwarding  to login jsp
     */
    @RequestMapping(value = "/index", method = RequestMethod.GET)
    public ModelAndView indexPage() {
        return new ModelAndView("login");
    }

    /*
     * Forwarding to 403 jsp page for error handling
     */
    @RequestMapping(value = "/403", method = RequestMethod.GET)
    public ModelAndView accessDeniedPage() {
        return new ModelAndView("403");
    }

    // Maker section - starts
    /*
     * Forwarding to maker jsp 
     * For displaying Maker Work List 
     * 
     */
    @RequestMapping(value = "/makerList", method = RequestMethod.GET)
    public ModelAndView makerPage() {
        ModelAndView modelAndView = new ModelAndView("maker");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        modelAndView.addObject("uid", loginUser.getId());
        modelAndView.addObject("uname", loginUser.getFirstname());
        modelAndView.addObject("urname", loginUser.getRole().getName());
        return modelAndView;
    }

    /*
	 * Returns the customer object based on ID
	 */
    @RequestMapping(value = "findCustomerById", method = RequestMethod.GET)
    //@ResponseBody
    public String findCustomerById(@RequestParam long id, ModelMap m) {
        // return usersService.findCustomerById(id);
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        m.addAttribute("status", customerDetails.getStatus());
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return "success-login";
    }
    
    /*
	 *  Forwarding to Customer Details Entry form moderation jsp
	 */    
    @RequestMapping(value = "/makerAddUser", method = RequestMethod.GET)
    public ModelAndView makerAddUser(@ModelAttribute("role") String role, ModelMap m) {
    	Map<Object, Object> chapert4statusMap = usersService.findMakerInitialQuestion();
    	List<Object[]> chapert4statusList = (List<Object[]>)chapert4statusMap.get("listCategoryQuestion");
    	ModelAndView mav = new ModelAndView("moderation");  
    	Map<String, String> categories = new HashMap<String, String>(); 
    	for(Object[] obj:chapert4statusList)
    	categories.put(obj[0].toString()+"_"+obj[1].toString(), obj[1].toString());  
    	 mav.addObject("chapter4statusList", categories);  
         mav.addObject("command", new CustomerDetails());  
        m.addAttribute("mode", "add");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return mav;
    }

    @RequestMapping(value = "/makerAddUserIndividual", method = RequestMethod.GET)
    public ModelAndView makerAddUserIndividual(@ModelAttribute("role") String role, ModelMap m) {
    	//Map<Object, Object> chapert4statusMap = usersService.findMakerInitialQuestion();
    	//List<Object[]> chapert4statusList = (List<Object[]>)chapert4statusMap.get("listCategoryQuestion");
    	ModelAndView mav = new ModelAndView("individual");  
    	//Map<String, String> categories = new HashMap<String, String>(); 
    	//for(Object[] obj:chapert4statusList)
    	//categories.put(obj[0].toString()+"_"+obj[1].toString(), obj[1].toString());  
    	// mav.addObject("chapter4statusList", categories);  
       //  mav.addObject("command", new CustomerDetails());  
       // m.addAttribute("mode", "add");
    	
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        
        return mav;
    }

    
    /*
     * Forwarding  to moderation jsp to edit the customer details page
     */
    @RequestMapping(value = "/makerEditUser", method = RequestMethod.GET)
    public ModelAndView makerEditUser(@RequestParam long id, ModelMap m) {
    	Map<Object, Object> chapert4statusMap = usersService.findMakerInitialQuestion();
    	List<Object[]> chapert4statusList = (List<Object[]>)chapert4statusMap.get("listCategoryQuestion");
    	ModelAndView mav = new ModelAndView("moderation");  
    	Map<String, String> categories = new HashMap<String, String>(); 
    	for(Object[] obj:chapert4statusList)
    	categories.put(obj[0].toString()+"_"+obj[1].toString(), obj[1].toString());  
    	mav.addObject("chapter4statusList", categories);  
        
         
        // return usersService.findCustomerById(id);
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        mav.addObject("command", customerDetails);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("custId", id);
        return mav;
    }

    /*
     * Forward to moderation jsp to enter new customer deatils page
     */
    @RequestMapping(value = "/moderation", method = RequestMethod.GET)
    public ModelAndView moderatorPage() {
        return new ModelAndView("moderation", "command", new CustomerDetails());
    }

    // Maker section - ends
    // Admin section - start
    @RequestMapping(value = "/admin", method = RequestMethod.GET)
    public String adminPage() {
        return "admin";
    }
    @RequestMapping(value = "auditor", method = RequestMethod.GET)
    public String auditorPage() {
        return "auditor";
    }
    @RequestMapping(value = "/adminDoc", method = RequestMethod.GET)
    public String adminDocPage() {
        return "adminDoc";
    }
    
    @RequestMapping(value = "/adminAddUser", method = RequestMethod.GET)
    public String adminAddUserPage() {
        return "adminAddUser";
    }

    @RequestMapping(value = "/adminEditUser", method = RequestMethod.GET)
    public String adminEditUserPage(@ModelAttribute("id") Long id, ModelMap m) {
        m.addAttribute("aid", id);
        return "adminEditUser";
    }

    /*
     * Forwarding to moderation jsp to create new entity user 
     */
    @RequestMapping(value = "newUser", method = RequestMethod.GET)
    public String newUser(@ModelAttribute("role") String role, ModelMap m) {

        m.addAttribute("mode", "add");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return "success-login";
    }

    /*
     * Forwarding to moderation jsp to create new individual user 
     */
    @RequestMapping(value = "newUserIndividual", method = RequestMethod.GET)
    public String newUserIndividual(@ModelAttribute("role") String role, ModelMap m) {

        m.addAttribute("mode", "individual");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return "success-login";
    }

    /*
     * Forwarding to moderation jsp to create new individual user 
     */
    @RequestMapping(value = "selectUser", method = RequestMethod.GET)
    //@ResponseBody
    public String selectUser(@RequestParam long id, ModelMap m) {
        //Customer customer = new Customer(id);
        LoginUser selectedUser = usersService.getLoggedInUser(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", selectedUser.getFirstname());
        m.addAttribute("mode", "edit");

        // return usersService.findCustomerById(id);
        //  return new ModelAndView("moderation", "command", usersService.findCustomerById(id));\
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return "success-login";


    }

    // Admin section - end
    @RequestMapping(value = "/admin/second", method = RequestMethod.GET)
    public ModelAndView secondAdminPage() {
        return new ModelAndView("admin-second");
    }

    @RequestMapping(value = "/saveClientDetails", method = RequestMethod.POST)
    public ModelAndView addCustomerDetails(@ModelAttribute("details") CustomerDetails details, ModelMap model) {
    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        CustomerDetails custDetails = usersService.addCustomerDetails(details, loginUser);
        //CustomerDetails custDetails = usersService.addCustomerDetails(details, loginUser.getId());
        model.addAttribute("sid", custDetails.getId());
        model.addAttribute("suname", custDetails.getNameOfEntityCustomer());
        model.addAttribute("mode", "edit");
        model.addAttribute("status", custDetails.getStatus());
		model.addAttribute("custId", custDetails.getId());
        
        String custId = String.valueOf(details.getId());
        File file = new File("C:\\CustomerDocuments\\"+custId);
		if (!file.exists()) {
	            file.mkdirs();
		}
        model.addAttribute("uid", loginUser.getId());
        model.addAttribute("uname", loginUser.getFirstname());
        model.addAttribute("urname", loginUser.getRole().getName());
       // model.addAttribute("mode", "add");
        return new ModelAndView("success-login");
    }

    /*
     * Find all customers from database
     */
    @RequestMapping(value = "findAllCustomers", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerDetails> findAllCustomers() {
        return usersService.findAllCustomers();
    }
    
    /*
     * Get all customers from database
     */
    @RequestMapping(value = "getAllCustomers", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerDetails> getAllCustomers() {
        return usersService.getAllCustomers();
    }
    
    /*
     * Get all customers from database for checker user
     */
    @RequestMapping(value = "findAllCustomersChecker", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerDetails> findAllCustomersChecker() {
        return usersService.findAllCustomersChecker();
    }

    
    @RequestMapping(value = "getRulesStatus", method = RequestMethod.GET)
    @ResponseBody
    public Map<Long, DocumentDto> getRulesStatus() {
        return usersService.getRules();
    }
    
    @RequestMapping(value = "getRulesDocument", method = RequestMethod.GET)
    @ResponseBody
    public List<Document> getRulesDocument() {
        return usersService.findDocumentRules();
    }
    
    @RequestMapping(value = "getdocumentUploded", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerDocument> getdocumentUploded(@RequestParam long id) {
        return usersService.documentUploded(id);
    }
 /*   //NFFE section - start
    @RequestMapping(value = "editNffe", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editNffe(@RequestParam long id, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getCustomerName());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return new ModelAndView("nffe", "command", customerDetails);

    }*/
    
    //NFFE section - start
    /*
     * Forward to checkerNFFE or checkerFFI jsp page
     */
    @RequestMapping(value = "editChecker", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editChecker(@RequestParam long id, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        String classification = questionService.findClassification(id);
        ModelAndView modelChecker = null;
        if(classification.equals("NFFE")) 
        	modelChecker = new ModelAndView("checkerNFFE", "command", customerDetails);
        if(classification.equals("FFI")) 
        	modelChecker = new ModelAndView("checkerFFI", "command", customerDetails);       
        return modelChecker;

    }
    
    //NFFE section - start
    @RequestMapping(value = "editAuditor", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editAuditor(@RequestParam long id, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
       
        ModelAndView   modelChecker = new ModelAndView("auditorNFFE", "command", customerDetails);
      /*  String classification = questionService.findClassification(id);
        if(classification.equals("NFFE")) 
        	modelChecker = new ModelAndView("checkerNFFE", "command", customerDetails);
        if(classification.equals("FFI")) 
        	modelChecker = new ModelAndView("checkerFFI", "command", customerDetails);  */     
        return modelChecker;

    }
    
    /*
     * Forward to maker nffe jsp page 
     */
    @RequestMapping(value = "editNffeMaker", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editNffeMaker(@RequestParam long id,@RequestParam String cats, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("cats", cats);
        return new ModelAndView("nffe", "command", customerDetails);


    }
  //NFFE section - start
    @RequestMapping(value = "editNffeShow", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editNffeShow(@RequestParam long id, @RequestParam String mode,  @RequestParam String cats, @RequestParam String showentity, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        //Question question = questionService.findQuestionById(new Long(cats));
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", mode);
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("cats",cats);
        m.addAttribute("showentity",showentity);
        return new ModelAndView("nffe", "command", customerDetails);


    }
    
    
    
    //FFE section - start
    @RequestMapping(value = "editFFIMaker", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editFFIMaker(@RequestParam long id,@RequestParam String cats, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("cats", cats);
        return new ModelAndView("ffi", "command", customerDetails);


    }
  //NFFE section - start
    @RequestMapping(value = "editFFIShow", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editFFIShow(@RequestParam long id, @RequestParam String mode,  @RequestParam String cats, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        //Question question = questionService.findQuestionById(new Long(cats));
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", mode);
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("cats",cats);
        return new ModelAndView("ffi", "command", customerDetails);


    }
    @RequestMapping(value = "editFFI", method = RequestMethod.GET)
    //@ResponseBody
    public ModelAndView editFFI(@RequestParam long id, ModelMap m) {
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return new ModelAndView("ffi", "command", customerDetails);


    }

    @RequestMapping(value = "selectNffe", method = RequestMethod.GET)
    //@ResponseBody
    public String selectNffe(@RequestParam long id, ModelMap m) {
        //Customer customer = new Customer(id);
        CustomerDetails customerDetails = usersService.findCustomerById(id);
        m.addAttribute("sid", id);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        String classification = questionService.findClassification(id);
        if(classification.equals("FFI"))
        	m.addAttribute("isnffe", "no");
        if(classification.equals("NFFE"))
        	m.addAttribute("isnffe", "yes");
        m.addAttribute("status", customerDetails.getStatus());
        // return usersService.findCustomerById(id);
        //  return new ModelAndView("moderation", "command", usersService.findCustomerById(id));\
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return "success-login";


    }
  //NFFE section - end

   @RequestMapping(value = "uploadFileHandler", method = RequestMethod.POST)
    public ModelAndView uploadFileHandler(@RequestParam("file") MultipartFile[] files,@RequestParam("docStatusId") String[] docStatusId,@RequestParam("custID") Long custID, ModelMap m) {    	
    	
    	for(int i = 0; i < files.length; i++){
    		if(!docStatusId[i].contains("_n")){
    			String documentId = docStatusId[i].split("_")[0];
    			Long docId = Long.valueOf(documentId);
    		    
    		    
    			MultipartFile file = files[i];
				InputStream inputStream = null;
				OutputStream outputStream = null;
				String fileName = file.getOriginalFilename();
				try {
	            inputStream = file.getInputStream();
	            String path = getClass().getClassLoader().getResource(".").getPath();
	            
	            File newFile = new File("C:\\CustomerDocuments\\"+custID+"\\" + fileName);
		            if (!newFile.exists()) {
		            	newFile.getParentFile().mkdirs();
		                newFile.createNewFile();
		            }
		            outputStream = new FileOutputStream(newFile);
		            int read = 0;
		            byte[] bytes = new byte[1024];
		            while ((read = inputStream.read(bytes)) != -1) {
		                outputStream.write(bytes, 0, read);
		            }
				} catch (IOException e) {
					e.printStackTrace();
				}
		        LocalDateTime localDateTime = LocalDateTime.now();
		        String filePath = "C:\\CustomerDocuments\\"+custID+"\\" + fileName;
		        
		        usersService.saveDocs(custID, docId, fileName, localDateTime, filePath);
    		}
    	}
    	CustomerDetails customerDetails = usersService.findCustomerById(custID);
        m.addAttribute("sid", custID);
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("mode", "edit");
        m.addAttribute("status", customerDetails.getStatus());
    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
    	LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("custId", custID);
        return new ModelAndView("success-login");
        
    }

    @RequestMapping(value = "getDocuments", method = RequestMethod.GET)
    @ResponseBody
    public List<Document> getDocuments() {
        return usersService.getDocuments();
    }
    @RequestMapping(value = "getDocuments1", method = RequestMethod.GET)
    @ResponseBody
    public List<Document> getDocuments1() {
        return usersService.getDocuments1();
    }
    
/*     
    @RequestMapping(value = "/getDocumentNames",method = RequestMethod.GET,headers="Accept=*")
    public @ResponseBody List<String> getDocNamesList(@RequestParam("term") String query) {
        List<String> docNames = usersService.getDocumentNamesList(query);
        return docNames;
    } 
    */
    
    @RequestMapping(value = "getDocumentNames", method = RequestMethod.GET)
    @ResponseBody
    public List<Document> getDocumentNames() {
        return usersService.getDocumentNames();
    }
    
    
    @RequestMapping(value = "findAllCheckers", method = RequestMethod.GET)
    @ResponseBody
    public List<LoginUser> findAllCheckers() {
        return usersService.findAllCheckers();
    }
    
    @RequestMapping(value = "findQuesByCategory", method = RequestMethod.GET)
    @ResponseBody
    public List<Question> findQuesByCategory(@RequestParam String category) {
        return usersService.findQuesByCategory(category);
    }
    
    
    //@Override
    @RequestMapping(value = "saveMakerInitialQuestNo", method = RequestMethod.GET)
    // @ResponseBody
    public String saveMakerInitialQuestNo(@RequestParam String custID, @RequestParam String catL, @RequestParam String quesId0, @RequestParam String ansId0,
    		@RequestParam String quesId1, @RequestParam String ansId1, @RequestParam String catdd, ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, catL, quesId0, ansId0,quesId1, ansId1, catdd);
    	CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
        m.addAttribute("sid", new Long(custID));
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("classf", catclass[0]);
        m.addAttribute("cats", catclass[1]);
        return "success-login";
    }
    
    //@Override
    @RequestMapping(value = "saveMakerInitialQuest", method = RequestMethod.GET)
    // @ResponseBody
    public String saveMakerInitialQuest(@RequestParam String custID, @RequestParam String catL, @RequestParam String quesId0, @RequestParam String ansId0,
    		@RequestParam String quesId1, @RequestParam String ansId1, ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, catL, quesId0, ansId0,quesId1, ansId1, null);
    	CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
        m.addAttribute("sid", new Long(custID));
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("classf", catclass[0]);
        m.addAttribute("cats", catclass[1]);
        return "success-login";
    }
    
   /* //maker questionarie - starts
    //@Override
    @RequestMapping(value = "saveMakerInitialQuest", method = RequestMethod.GET)
   // @ResponseBody
    public String saveMakerInitialQuest(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,@RequestParam String expiration,
    		@RequestParam String circumchange,@RequestParam String circumstances,@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL, ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
    	    		 quesId1, ansId1, expiration,
    	    		 circumchange, circumstances, other,
    	    		 othertext, catL);
    	CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
        m.addAttribute("sid", new Long(custID));
        m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
        m.addAttribute("status", customerDetails.getStatus());
       // m.addAttribute("mode", "edit");
        String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        m.addAttribute("classf", catclass[0]);
        m.addAttribute("cats", catclass[1]);
        return "success-login";
    }
    @RequestMapping(value = "saveMakerInitialQuest1", method = RequestMethod.GET)
   // @ResponseBody
    public String saveMakerInitialQuest1(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		@RequestParam String expiration,
    		//@RequestParam String circumchange,
            @RequestParam String circumstances,
    		//@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL,
    		ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, expiration,
	    		 null, circumstances, null,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
       //  m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    @RequestMapping(value = "saveMakerInitialQuest2", method = RequestMethod.GET)
   // @ResponseBody
    public String saveMakerInitialQuest2(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		//@RequestParam String expiration,
    		@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		//@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL,
    		ModelMap m) {
    	 Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, null,
	    		 circumchange, circumstances, null,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
       //  m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);       
         return "success-login";
    }
    @RequestMapping(value = "saveMakerInitialQuest3", method = RequestMethod.GET)
   // @ResponseBody
    public String saveMakerInitialQuest3(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		//@RequestParam String expiration,
    		//@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL,ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, null,
	    		 null, circumstances, other,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
      //   m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    
    @RequestMapping(value = "saveMakerInitialQuest4", method = RequestMethod.GET)
   // @ResponseBody
    public String saveMakerInitialQuest4(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		@RequestParam String expiration,
    		@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		//@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL, ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, expiration,
	    		 circumchange, circumstances, null,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    
    @RequestMapping(value = "saveMakerInitialQuest5", method = RequestMethod.GET)
  //  @ResponseBody
    public String saveMakerInitialQuest5(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		//@RequestParam String expiration,
    		@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL, ModelMap m) {
    	 Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, null,
	    		 circumchange, circumstances, other,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    
    @RequestMapping(value = "saveMakerInitialQuest6", method = RequestMethod.GET)
    //@ResponseBody
    public String saveMakerInitialQuest6(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		@RequestParam String expiration,
    		//@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		@RequestParam String other,
    		@RequestParam String othertext,@RequestParam String catL, ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, expiration,
	    		 null, circumstances, other,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    @RequestMapping(value = "saveMakerInitialQuest7", method = RequestMethod.GET)
  //  @ResponseBody
    public String saveMakerInitialQuest7(@RequestParam String custID,@RequestParam String quesId0,@RequestParam String ansId0,
    		@RequestParam String quesId1,@RequestParam String ansId1,
    		//@RequestParam String expiration,
    		//@RequestParam String circumchange,
    		@RequestParam String circumstances,
    		//@RequestParam String other,
    		@RequestParam String othertext, @RequestParam String catL,ModelMap m) {
    	Object[] catclass = usersService.saveMakerInitialQuest(custID, quesId0, ansId0,
	    		 quesId1, ansId1, null,
	    		 null, circumstances, null,
	    		 othertext, catL);
    	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
         m.addAttribute("sid", new Long(custID));
         m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
         m.addAttribute("status", customerDetails.getStatus());
        // m.addAttribute("mode", "edit");
         String name = SecurityContextHolder.getContext().getAuthentication().getName();
         LoginUser loginUser = usersService.getUser(name);
         m.addAttribute("uid", loginUser.getId());
         m.addAttribute("uname", loginUser.getFirstname());
         m.addAttribute("urname", loginUser.getRole().getName());
         m.addAttribute("classf", catclass[0]);
         m.addAttribute("cats", catclass[1]);
         return "success-login";
    }
    //maker questionarie - ends
    
    */
    @RequestMapping(value = "saveMakerFFIAnswers", method = RequestMethod.GET)
    //  @ResponseBody
      public String saveMakerFFIAnswers(@RequestParam String custID, @RequestParam String catID, @RequestParam String quesId0,@RequestParam String ansId0,
      		@RequestParam String quesId1,@RequestParam String ansId1,
      		@RequestParam String explanation,
      		@RequestParam String quesId2,@RequestParam String ansId2, 
      		@RequestParam String fatcaStatusFFI,
      		ModelMap m) {
      	String classification = questionService.saveMakerFFIAnswers(custID, quesId0, ansId0,
  	    		 quesId1, ansId1, explanation,
  	    		 quesId2,ansId2, fatcaStatusFFI);
      	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
           m.addAttribute("sid", new Long(custID));
           m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
           m.addAttribute("status", customerDetails.getStatus());
           m.addAttribute("mode", "showc");
           String name = SecurityContextHolder.getContext().getAuthentication().getName();
           LoginUser loginUser = usersService.getUser(name);
           m.addAttribute("uid", loginUser.getId());
           m.addAttribute("uname", loginUser.getFirstname());
           m.addAttribute("urname", loginUser.getRole().getName());
           m.addAttribute("classf", classification);
           m.addAttribute("cats", catID);
           return "success-login";
      }
    
    @RequestMapping(value = "saveMakerNFFEAnswers", method = RequestMethod.GET)
    //  @ResponseBody
      public String saveMakerNFFEAnswers(@RequestParam String custID,@RequestParam String catID,@RequestParam String quesId0,@RequestParam String ansId0,
        		@RequestParam String quesId1,@RequestParam String ansId1,
          		@RequestParam String explanation,
          		@RequestParam String quesId2,@RequestParam String ansId2, 
          		@RequestParam String quesId3,@RequestParam String ansId3, 
          		@RequestParam String quesId4,@RequestParam String ansId4, 
          		@RequestParam String quesId5,@RequestParam String ansId5, 
          		@RequestParam String quesId6,@RequestParam String ansId6, 
          		@RequestParam String fatcaStatus,
          		ModelMap m) {
      	String classification = questionService.saveMakerNFFEAnswers( custID,quesId0,ansId0,
	    		quesId1,ansId1,
	      		explanation,
	      		quesId2,ansId2, 
	      		quesId3,ansId3, 
	      		quesId4,ansId4, 
	      		quesId5,ansId5, 
	      		quesId6,ansId6, fatcaStatus);
      	 CustomerDetails customerDetails = usersService.findCustomerById(new Long(custID));
           m.addAttribute("sid", new Long(custID));
           m.addAttribute("suname", customerDetails.getNameOfEntityCustomer());
           m.addAttribute("mode", "showc");
           m.addAttribute("status", customerDetails.getStatus());
           String name = SecurityContextHolder.getContext().getAuthentication().getName();
           LoginUser loginUser = usersService.getUser(name);
           m.addAttribute("uid", loginUser.getId());
           m.addAttribute("uname", loginUser.getFirstname());
           m.addAttribute("urname", loginUser.getRole().getName());
           m.addAttribute("classf", classification);
           m.addAttribute("cats", catID);
           if(ansId0.equals("No") || ansId1.equals("No") || ansId2.equals("No"))
        	   m.addAttribute("showentity", "no");
           else
        	   m.addAttribute("showentity", "yes");
           return "success-login" 	;
      }
    
    @RequestMapping(value = "getResponseNFFE", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerQuestion> getResponseNFFE(@RequestParam long id){
    	return usersService.getResponseNFFE(id);
    }
    
    @RequestMapping(value = "getResponseFFI", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerQuestion> getResponseFFI(@RequestParam long id){
    	return usersService.getResponseFFI(id);
    }
    
    @RequestMapping(value = "getResponseSpecNFFE", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerQuestion> getResponseSpecNFFE(@RequestParam long id){
    	return usersService.getResponseSpecNFFE(id);
    }
    
    @RequestMapping(value = "getResponseSpecFFI", method = RequestMethod.GET)
    @ResponseBody
    public List<CustomerQuestion> getResponseSpecFFI(@RequestParam long id){
    	return usersService.getResponseSpecFFI(id);
    }
    
    @RequestMapping(value = "saveSpecificFFIAnswers", method = RequestMethod.POST)
    //@ResponseBody
    public ModelAndView saveSpecificFFIAnswers(@RequestBody QuestionDTO y, ModelMap m){
    	questionService.saveSpecificFFIAnswers(y);
    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
        return new ModelAndView("success-login");
   }
    
    @RequestMapping(value = "saveSpecificNFFEAnswers", method = RequestMethod.POST)
    //@ResponseBody
    public ModelAndView saveSpecificNFFEAnswers(@RequestBody QuestionDTO y, ModelMap m){
    	questionService.saveSpecificNFFEAnswers(y);
    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
       return new ModelAndView("success-login");
    }
    
    @RequestMapping(value = "saveCheckerFinalStatus", method = RequestMethod.POST)
    //@ResponseBody
    public ModelAndView saveCheckerFinalStatus(@RequestBody QuestionRDTO y, ModelMap m){
    	questionService.saveCheckerFinalStatus(y);
    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
        LoginUser loginUser = usersService.getUser(name);
        m.addAttribute("uid", loginUser.getId());
        m.addAttribute("uname", loginUser.getFirstname());
        m.addAttribute("urname", loginUser.getRole().getName());
       return new ModelAndView("success-login");
    }
    
    
    @RequestMapping(value = "/uploadClientDetails", method = RequestMethod.POST)
    public ModelAndView uploadClientDetails(@RequestParam("file") MultipartFile file,
           ModelMap model) {
    	    String name = SecurityContextHolder.getContext().getAuthentication().getName();
	        LoginUser loginUser = usersService.getUser(name);
			try {
				XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
				XSSFSheet worksheet=workbook.getSheetAt(0);
				int c = 0;
				Iterator rows = worksheet.rowIterator();
				while (rows.hasNext()) {
					XSSFRow row1=(XSSFRow) rows.next();
					c = c + 1;
				}
				System.out.println("no of row :" + c);
				int i = 1;
				
				while (i < c) {
					CustomerDetails customer=new CustomerDetails();
					XSSFRow row1=worksheet.getRow(i);
					
					
					
					XSSFCell cell0 = row1.getCell((short) 0);
					/*String nameOfEntityCustomer = cell0.getStringCellValue(); //customerName
					customer.setNameOfEntityCustomer(nameOfEntityCustomer);*/
					if(cell0!=null){	
					if(cell0.getCellType()!=XSSFCell.CELL_TYPE_NUMERIC){
						/*if(DateUtil.isCellDateFormatted(cell0)){
							String nameOfEntityCustomer=cell0.getDateCellValue().toString();
					customer.setNameOfEntityCustomer(nameOfEntityCustomer);
						}*/
						/*else{
							Double nameOfEntityCustomer=cell0.getNumericCellValue();
							customer.setNameOfEntityCustomer(nameOfEntityCustomer.toString());
						}
					}else{*/
						String nameOfEntityCustomer=cell0.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setNameOfEntityCustomer(nameOfEntityCustomer);
					}
					
					
					
					
					XSSFCell cell1 = row1.getCell((short) 1);
					/*String tin = cell1.getStringCellValue();//tin
					customer.setTin(tin);*/
					if(cell1!=null){	
						if(cell1.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
							/*if(DateUtil.isCellDateFormatted(cell1)){
								String tin=cell1.getDateCellValue().toString();
					customer.setTin(tin);
							}
							else{
								Double tin=cell1.getNumericCellValue();
								customer.setTin(tin.toString());
							}
						}else{*/
							String tin=cell1.getStringCellValue();
							//if(incorporationDate.equals("NA"))
							customer.setTin(tin);
						}
						
					
					XSSFCell cell2 = row1.getCell((short) 2);
					/*String countryOfIssuance = cell2.getStringCellValue();//countryOfIssuance
					customer.setCountryOfIssuance(countryOfIssuance);*/
					if(cell2.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell2)){
							String countryOfIssuance=cell2.getDateCellValue().toString();
					customer.setCountryOfIssuance(countryOfIssuance);
						}
						else{
							Double countryOfIssuance=cell2.getNumericCellValue();
							customer.setCountryOfIssuance(countryOfIssuance.toString());
						}
					}else{
						String countryOfIssuance=cell2.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setCountryOfIssuance(countryOfIssuance);
					}
					
					
					
					XSSFCell cell3 = row1.getCell((short) 3);
			/*		String countryOfIncorporation=cell3.getStringCellValue();//countryOfIncorporation
					customer.setCountryOfIncorporation(countryOfIncorporation);*/
					
					if(cell3.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell3)){
							String countryOfIncorporation=cell3.getDateCellValue().toString();
					customer.setCountryOfIncorporation(countryOfIncorporation);
						}
					/*	else{
							Double countryOfIncorporation=cell3.getNumericCellValue();
							customer.setCountryOfIncorporation(countryOfIncorporation);
						}*/
					}else{
						String countryOfIncorporation=cell3.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setCountryOfIncorporation(countryOfIncorporation);
					}
					
					
					XSSFCell cell4 = row1.getCell((short) 4);
				/*	String lei=cell4.getStringCellValue();//lei
					customer.setLei(lei);*/
					
					if(cell4.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell4)){
							String lei=cell4.getDateCellValue().toString();
							customer.setLei(lei);
						}
						else{
							Double lei=cell4.getNumericCellValue();
							customer.setLei(lei.toString());
						}
					}else{
						String lei=cell4.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setLei(lei);
					}
					
					
					
					XSSFCell cell5 = row1.getCell((short) 5);
					//String aggregateBalanceExpected=cell5.getStringCellValue();//aggregateBalanceExpected
					/*Double aggregateBalanceExpected=cell5.getNumericCellValue();
					customer.setAggregateBalanceExpected(aggregateBalanceExpected.toString());
					*/
					
					
					if(cell5.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell5)){
							String aggregateBalanceExpected=cell5.getDateCellValue().toString();
					customer.setAggregateBalanceExpected(aggregateBalanceExpected);
						}
						else{
							Double aggregateBalanceExpected=cell5.getNumericCellValue();
							customer.setAggregateBalanceExpected(aggregateBalanceExpected.toString());
						}
					}else{
						String aggregateBalanceExpected=cell5.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setAggregateBalanceExpected(aggregateBalanceExpected);
					}
					

					
					
					XSSFCell cell6 = row1.getCell((short) 6);
					/*String investmentIncomeAsRatioOfGrossIncome=cell6.getStringCellValue();
					customer.setInvestmentIncomeAsRatioOfGrossIncome(investmentIncomeAsRatioOfGrossIncome);*/
					if(cell6.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell6)){
							String investmentIncomeAsRatioOfGrossIncome=cell6.getDateCellValue().toString();
					customer.setInvestmentIncomeAsRatioOfGrossIncome(investmentIncomeAsRatioOfGrossIncome);
						}
						else{
							Double investmentIncomeAsRatioOfGrossIncome=cell6.getNumericCellValue();
							customer.setInvestmentIncomeAsRatioOfGrossIncome(investmentIncomeAsRatioOfGrossIncome.toString());
						}
					}else{
						String investmentIncomeAsRatioOfGrossIncome=cell6.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setInvestmentIncomeAsRatioOfGrossIncome(investmentIncomeAsRatioOfGrossIncome);
					}
					
					
					
					XSSFCell cell7 = row1.getCell((short) 7);
					//String assetsQuarterlyInvestmentIncome=cell7.getStringCellValue();//assetsQuarterlyInvestmentIncome
					/*Double assetsQuarterlyInvestmentIncome=cell7.getNumericCellValue();
					customer.setAssetsQuarterlyInvestmentIncome(assetsQuarterlyInvestmentIncome.toString());*/
					
					if(cell7.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell7)){
							String assetsQuarterlyInvestmentIncome=cell7.getDateCellValue().toString();
							customer.setAssetsQuarterlyInvestmentIncome(assetsQuarterlyInvestmentIncome);
						}
						else{
							Double assetsQuarterlyInvestmentIncome=cell7.getNumericCellValue();
							customer.setAssetsQuarterlyInvestmentIncome(assetsQuarterlyInvestmentIncome.toString());
						}
					}else{
						String assetsQuarterlyInvestmentIncome=cell7.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setAssetsQuarterlyInvestmentIncome(assetsQuarterlyInvestmentIncome);
					}
					
					
					
					
					XSSFCell cell8 = row1.getCell((short) 8);
					//String incorporationDate=cell8.getStringCellValue();//incorporationDate
				
					if(cell8.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell8)){
							String incorporationDate=cell8.getDateCellValue().toString();
					customer.setIncorporationDate(incorporationDate);
						}
						/*else{
							Double incorporationDate=cell5.getNumericCellValue();
							customer.setIncorporationDate(incorporationDate.toString());
						}*/
					}else{
						String incorporationDate=cell8.getStringCellValue();
						if(incorporationDate.equals("NA"))
						customer.setIncorporationDate(incorporationDate);
					}
					
					
					
					
					XSSFCell cell9 = row1.getCell((short) 9);
					/*String bankruptcyFilingDate=cell9.getStringCellValue();//bankruptcyFilingDate
					customer.setBankruptcyFilingDate(bankruptcyFilingDate);*/
					
					
					if(cell9.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell9)){
							String bankruptcyFilingDate=cell9.getDateCellValue().toString();
							customer.setBankruptcyFilingDate(bankruptcyFilingDate);
						}
						else{
							Double bankruptcyFilingDate=cell9.getNumericCellValue();
							customer.setBankruptcyFilingDate(bankruptcyFilingDate.toString());
						}
					}else{
						String bankruptcyFilingDate=cell9.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setBankruptcyFilingDate(bankruptcyFilingDate);
					}
					
					
					XSSFCell cell10 = row1.getCell((short) 10);
					/*String usOwnersShareDistribution=cell10.getStringCellValue();//usOwnersShareDistribution
					customer.setUsOwnersShareDistribution(usOwnersShareDistribution);*/
					if(cell10.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell10)){
							String usOwnersShareDistribution=cell10.getDateCellValue().toString();
					customer.setUsOwnersShareDistribution(usOwnersShareDistribution);
						}
						else{
							Double usOwnersShareDistribution=cell10.getNumericCellValue();
							customer.setUsOwnersShareDistribution(usOwnersShareDistribution.toString());
						}
					}else{
						String usOwnersShareDistribution=cell10.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setUsOwnersShareDistribution(usOwnersShareDistribution);
					}
					
					
					XSSFCell cell11= row1.getCell((short) 11);
				/*	String usOwnersShareValueInDollars=cell11.getStringCellValue();//usOwnersShareValueInDollars
					customer.setUsOwnersShareValueInDollars(usOwnersShareValueInDollars);*/
					
					if(cell11.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell11)){
							String usOwnersShareValueInDollars=cell11.getDateCellValue().toString();
							customer.setUsOwnersShareValueInDollars(usOwnersShareValueInDollars);
						}
						else{
							Double usOwnersShareValueInDollars=cell11.getNumericCellValue();
							customer.setUsOwnersShareValueInDollars(usOwnersShareValueInDollars.toString());
						}
					}else{
						String usOwnersShareValueInDollars=cell11.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setUsOwnersShareValueInDollars(usOwnersShareValueInDollars);
					}
					
					
					XSSFCell cell12 = row1.getCell((short) 12);
					/*String electronicAccountNumber=cell12.getStringCellValue();//electronicAccountNumber
					customer.setElectronicAccountNumber(electronicAccountNumber);*/
					

					if(cell11.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell11)){
							String electronicAccountNumber=cell11.getDateCellValue().toString();
							customer.setElectronicAccountNumber(electronicAccountNumber);
						}
						else{
							Double electronicAccountNumber=cell11.getNumericCellValue();
							customer.setElectronicAccountNumber(electronicAccountNumber.toString());
						}
					}else{
						String electronicAccountNumber=cell11.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setElectronicAccountNumber(electronicAccountNumber);
					}
					
					/*
					XSSFCell cell13 = row1.getCell((short) 13);
					String passiveIncomeofEAG=cell13.getStringCellValue();//natureOfBuisness
					customer.setPassiveIncomeofEAG(passiveIncomeofEAG);*/
					
					
					XSSFCell cell13 = row1.getCell((short) 13);
				/*	String grossIncomeofEAG=cell13.getStringCellValue();//grossIncomeofEAG
					customer.setGrossIncomeofEAG(grossIncomeofEAG);*/
					
					if(cell13.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell13)){
							String grossIncomeofEAG=cell13.getDateCellValue().toString();
							customer.setGrossIncomeofEAG(grossIncomeofEAG);
						}
						else{
							Double grossIncomeofEAG=cell13.getNumericCellValue();
							customer.setGrossIncomeofEAG(grossIncomeofEAG.toString());
						}
					}else{
						String grossIncomeofEAG=cell13.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setGrossIncomeofEAG(grossIncomeofEAG);
					}
					
					
					XSSFCell cell14 = row1.getCell((short) 14);
					/*String totalAssestsEntity=cell14.getStringCellValue();//totalAssestsEntity
					customer.setTotalAssestsEntity(totalAssestsEntity);*/
					
					if(cell14.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell14)){
							String totalAssestsEntity=cell14.getDateCellValue().toString();
							customer.setTotalAssestsEntity(totalAssestsEntity);
						}
						else{
							Double totalAssestsEntity=cell14.getNumericCellValue();
							customer.setTotalAssestsEntity(totalAssestsEntity.toString());
						}
					}else{
						String totalAssestsEntity=cell14.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setTotalAssestsEntity(totalAssestsEntity);
					}
					
					XSSFCell cell15 = row1.getCell((short) 15);
					/*String totalAssestsEag=cell15.getStringCellValue();//totalAssestsEag
					customer.setTotalAssestsEag(totalAssestsEag);*/
					
					if(cell15.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell15)){
							String totalAssestsEag=cell15.getDateCellValue().toString();
							customer.setTotalAssestsEag(totalAssestsEag);
						}
						/*else{
							Double totalAssestsEag=cell15.getNumericCellValue();
							customer.setTotalAssestsEag(totalAssestsEag.toString());
						}*/
					}else{
						String totalAssestsEag=cell15.getStringCellValue();
						//if(incorporationDate.equals("NA"))
						customer.setTotalAssestsEag(totalAssestsEag);
					}
					
					
					XSSFCell cell16 = row1.getCell((short) 16);
					/*String chapter4status=cell16.getStringCellValue();//chapter4status
					customer.setChapter4status(chapter4status);*/
					
					if(cell16.getCellType()==XSSFCell.CELL_TYPE_NUMERIC){
						if(DateUtil.isCellDateFormatted(cell16)){
							String chapter4status=cell16.getDateCellValue().toString();
							customer.setChapter4status(chapter4status);
						}
						/*else{
							Double chapter4status=cell16.getNumericCellValue();
							customer.setChapter4status(chapter4status.toString());
						}*/
					}else{
						String chapter4status=cell16.getStringCellValue();
						//if(incorporationDate.equals("NA"))
					customer.setChapter4status(chapter4status);
					}
					
					CustomerDetails customerDetails=usersService.addCustomerDetails(customer, loginUser);
					
					
					
					XSSFCell cell17 = row1.getCell((short) 17);
					String businessNature=cell17.getStringCellValue();//---------------------------------------<businessNature>					
					List<String> businessNatures = Arrays.asList(businessNature.split(";"));
					List<BusinessNature> businessNaturesLocal = new ArrayList<BusinessNature>();
					
					for(String row:businessNatures)
					{
						List<String> cellvalues=Arrays.asList(row.split(":"));
						
						BusinessNature businessNaturelocal = new BusinessNature();
						businessNaturelocal.setBusinessActivity(cellvalues.get(0));
						businessNaturelocal.setEntityName(cellvalues.get(1));
						businessNaturelocal.setIndustryClass(cellvalues.get(2));
						businessNaturelocal.setCustomerid(customerDetails);
						businessNaturelocal.setCreatedBy(loginUser);
						businessNaturelocal.setCreationDate(LocalDateTime.now());
						businessNaturesLocal.add(businessNaturelocal);
						
						
					}
					usersService.saveAllBusinessNature(businessNaturesLocal);
						

					XSSFCell cell18 = row1.getCell((short) 18);
					String contact=cell18.getStringCellValue();//---------------------------------------------------<Contact>		
					List<String> contacts = Arrays.asList(contact.split(";"));
					List<Contact> contactLocal = new ArrayList<Contact>();
					
					for(String row:contacts)
					{
						List<String> cellvalues_contact=Arrays.asList(row.split(":"));
						
						Contact contactslocal_List = new Contact();
						contactslocal_List.setFirstName(cellvalues_contact.get(0));
						contactslocal_List.setLastName(cellvalues_contact.get(1));
						contactslocal_List.setEmailId(cellvalues_contact.get(2));
						contactslocal_List.setMobileNo(cellvalues_contact.get(3));
						contactslocal_List.setPhoneNo(cellvalues_contact.get(4));
						contactslocal_List.setContactType(cellvalues_contact.get(5));
						contactslocal_List.setCustomerId(customerDetails);
						contactslocal_List.setCreatedBy(loginUser);
						contactslocal_List.setCreationDate(LocalDateTime.now());
						
						contactLocal.add(contactslocal_List);
						
						
					}
					usersService.saveAllContact(contactLocal);
					
					
					
					XSSFCell cell19 = row1.getCell((short) 19);
					String eag_MemberStatus=cell19.getStringCellValue();//---------------------------------------------<EAG Member Status>					
					List<String> MemberStatus_List = Arrays.asList(eag_MemberStatus.split(";"));
					List<MemberStatus> MemberStatusLocal = new ArrayList<MemberStatus>();
					
					for(String row:MemberStatus_List)
					{
						List<String> cellvalues_stauts=Arrays.asList(row.split(":"));
						
						MemberStatus eag_MemberStatusLocal_list = new MemberStatus();
						eag_MemberStatusLocal_list.setMemberName(cellvalues_stauts.get(0));
						eag_MemberStatusLocal_list.setStatus(cellvalues_stauts.get(1));
						eag_MemberStatusLocal_list.setCustomerid(customerDetails);
						eag_MemberStatusLocal_list.setCreatedBy(loginUser);
						eag_MemberStatusLocal_list.setCreationDate(LocalDateTime.now());
						MemberStatusLocal.add(eag_MemberStatusLocal_list);				
						
						
					}
					usersService.saveAllEagMemberStatus(MemberStatusLocal);
					
					
					XSSFCell cell20 = row1.getCell((short) 20);
					String eag_PassiveIncome=cell20.getStringCellValue();//---------------------------------------------<EAG Passive Income>				
					List<String> passiveIncome = Arrays.asList(eag_PassiveIncome.split(";"));
					List<EagPassiveIncome> eagPassiveIncomeLocal = new ArrayList<EagPassiveIncome>();
					
					for(String row:passiveIncome)
					{
						List<String> cellvalues_Income=Arrays.asList(row.split(":"));
						
						
						EagPassiveIncome eagPassiveIncomeLocal_list=new EagPassiveIncome();
						eagPassiveIncomeLocal_list.setSourceName(cellvalues_Income.get(0));
						eagPassiveIncomeLocal_list.setPercentage(cellvalues_Income.get(1));
						eagPassiveIncomeLocal_list.setCustomerid(customerDetails);
						eagPassiveIncomeLocal_list.setCreatedBy(loginUser);
						eagPassiveIncomeLocal_list.setCreationDate(LocalDateTime.now());
						eagPassiveIncomeLocal.add(eagPassiveIncomeLocal_list);
						
					}
					usersService.saveAllPassiveIncome(eagPassiveIncomeLocal);
					
					
					XSSFCell cell21= row1.getCell((short) 21);
					String exchangeMarket=cell21.getStringCellValue();//---------------------------------------------<Exchange Market>					
					List<String> market = Arrays.asList(exchangeMarket.split(";"));
					List<Market> ExchangeMarketLocal = new ArrayList<Market>();
					
					for(String row:market)
					{
						List<String> cellvalues_market=Arrays.asList(row.split(":"));
						
						
						Market marketLocal_list=new Market();
						marketLocal_list.setMarketName(cellvalues_market.get(0));
						marketLocal_list.setTicket(cellvalues_market.get(1));
						marketLocal_list.setCustomerid(customerDetails);
						marketLocal_list.setCreatedBy(loginUser);
						marketLocal_list.setCreationDate(LocalDateTime.now());
						ExchangeMarketLocal.add(marketLocal_list);
						
					}
					
				   usersService.saveAllExchangeMarket(ExchangeMarketLocal);
					
					
					
					
					
					XSSFCell cell22 = row1.getCell((short) 22);
					String locations=cell22.getStringCellValue();//---------------------------------------------------<Locations>					
					List<String> location = Arrays.asList(locations.split(";"));
					List<Locations> locationsLocal = new ArrayList<Locations>();
					
					for(String row:location)
					{
						List<String> cellvalues_location=Arrays.asList(row.split(":"));
						
						
						Locations LocationsLocal_list=new Locations();
						LocationsLocal_list.setCity(cellvalues_location.get(0));
						LocationsLocal_list.setCountry(cellvalues_location.get(1));
						LocationsLocal_list.setCustomerid(customerDetails);
						LocationsLocal_list.setCreatedBy(loginUser);
						LocationsLocal_list.setCreationDate(LocalDateTime.now());
						locationsLocal.add(LocationsLocal_list);
					}
					 usersService.saveAllLocation(locationsLocal);
					
					
			
					
					XSSFCell cell23 = row1.getCell((short) 23);
					String regulators=cell23.getStringCellValue();//--------------------------------------------------------<Regulators>					
					List<String> regulator = Arrays.asList(regulators.split(";"));
					List<Regulators> regulatorsLocal = new ArrayList<Regulators>();
					
					for(String row:regulator)
					{
						List<String> cellvalues_regulator=Arrays.asList(row.split(":"));
						
						
						Regulators regulatorsLocal_list=new Regulators();
						regulatorsLocal_list.setRegulatorName(cellvalues_regulator.get(0));
						regulatorsLocal_list.setCountry(cellvalues_regulator.get(1));
						regulatorsLocal_list.setCustomerid(customerDetails);
						regulatorsLocal_list.setCreatedBy(loginUser);
						regulatorsLocal_list.setCreationDate(LocalDateTime.now());
						regulatorsLocal.add(regulatorsLocal_list);
					}
					 usersService.saveAllRegulators(regulatorsLocal);
					
					
					
					
					
					XSSFCell cell24 = row1.getCell((short) 24);
					String restrictedDistributor=cell24.getStringCellValue();//-----------------------------------------------<Restricted Distributor>					
					List<String> restrictedDistributors = Arrays.asList(restrictedDistributor.split(";"));
					List<RestrictedDistributor> restrictedDistributorsLocal = new ArrayList<RestrictedDistributor>();
					
					for(String row:restrictedDistributors)
					{
						List<String> cellvalues_restrictedDistributor=Arrays.asList(row.split(":"));
						
						
						RestrictedDistributor restrictedDistributors_list=new RestrictedDistributor();
						restrictedDistributors_list.setName(cellvalues_restrictedDistributor.get(0));
						restrictedDistributors_list.setTinNo(cellvalues_restrictedDistributor.get(1));
						restrictedDistributors_list.setLocation(cellvalues_restrictedDistributor.get(2));
						restrictedDistributors_list.setCustomerid(customerDetails);
						restrictedDistributors_list.setCreatedBy(loginUser);
						restrictedDistributors_list.setCreationDate(LocalDateTime.now());
						restrictedDistributorsLocal.add(restrictedDistributors_list);
						
					}
					 usersService.saveAllRestrictedDistributor(restrictedDistributorsLocal);
					
					
					
					
					XSSFCell cell25 = row1.getCell((short) 25);
					String sourceIncome=cell25.getStringCellValue();//--------------------------------------------------------<Source Income>						
					List<String> sourceIncomes = Arrays.asList(sourceIncome.split(";"));
					List<SourceIncome> sourceIncomesLocal = new ArrayList<SourceIncome>();
					
					for(String row:sourceIncomes)
					{
						List<String> cellvalues_sourceIncome=Arrays.asList(row.split(":"));
						
						
						SourceIncome sourceIncomes_list=new SourceIncome();
						sourceIncomes_list.setSourceName(cellvalues_sourceIncome.get(0));
						sourceIncomes_list.setPercentage(Double.valueOf(cellvalues_sourceIncome.get(1)));
						sourceIncomes_list.setCustomerid(customerDetails);						
						sourceIncomes_list.setCreatedBy(loginUser);
						sourceIncomes_list.setCreationDate(LocalDateTime.now());
						sourceIncomesLocal.add(sourceIncomes_list);
						
					}
					 usersService.saveAllSourceIncome(sourceIncomesLocal);
					
					
					
					XSSFCell cell26 = row1.getCell((short) 26);
					String stocksDistribution=cell26.getStringCellValue();//---------------------------------------------------<Stocks Distribution>	
					List<String> stocksDistributions = Arrays.asList(stocksDistribution.split(";"));
					List<StocksDistribution> stocksDistributionLocal = new ArrayList<StocksDistribution>();
					
					for(String row:stocksDistributions)
					{
						List<String> cellvalues_stocksDistribution=Arrays.asList(row.split(":"));
						
						
						StocksDistribution stocksDistribution_list=new StocksDistribution();
						stocksDistribution_list.setcAddress(cellvalues_stocksDistribution.get(0));
						stocksDistribution_list.setOwnedPercent(Double.valueOf(cellvalues_stocksDistribution.get(1)));
						stocksDistribution_list.setStockholderName(cellvalues_stocksDistribution.get(2));
						stocksDistribution_list.setTin(cellvalues_stocksDistribution.get(3));
						stocksDistribution_list.setValue(Double.valueOf(cellvalues_stocksDistribution.get(4)));
						stocksDistribution_list.setCreatedBy(loginUser);
						stocksDistribution_list.setCreationDate(LocalDateTime.now());
						stocksDistribution_list.setCustomerid(customerDetails);
						
											
						
						stocksDistributionLocal.add(stocksDistribution_list);
					}
					usersService.saveAllStocksDistribution(stocksDistributionLocal);
					
					i++;
				}
			} 
			}
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
			 
			 model.addAttribute("uid", loginUser.getId());
		     model.addAttribute("uname", loginUser.getFirstname());
		     model.addAttribute("urname", loginUser.getRole().getName());
			 return new ModelAndView("success-login");
		}
    
	    @RequestMapping(value = "saveDocumentRulesByDocId", method = RequestMethod.POST)
	   // @ResponseBody
	    public ModelAndView saveDocumentRulesByDocId(@RequestBody DocumentDto y, ModelMap m){
	    	 usersService.saveDocumentRulesByDocId(y);
	    	 String name = SecurityContextHolder.getContext().getAuthentication().getName();
		     LoginUser loginUser = usersService.getUser(name);
			 m.addAttribute("uid", loginUser.getId());
		     m.addAttribute("uname", loginUser.getFirstname());
		     m.addAttribute("urname", loginUser.getRole().getName());
	    	 return new ModelAndView("adminDoc");
	    }
	    
	    //1st change -start    
	    /*
	     * Forwarding to businessNature jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "businessNature", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView businessNature(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("businessNature");
  	    }
	    
	    /*
	     * Forwarding to eagMemberStatus jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "eagMemberStatus", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView eagMemberStatus(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("eagMemberStatus");
  	    }
	    
	    /*
	     * Forwarding to eagPassiveIncome jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "eagPassiveIncome", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView eagPassiveIncome(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("eagPassiveIncome");
  	    }
	    
	    
	    /*
	     * Forwarding to exchangeMarket jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "exchangeMarket", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView exchangeMarket(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("exchangeMarket");
  	    }
	    
	    /*
	     * Forwarding to locations jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "locations", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView locations(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("locations");
  	    }
	    
	    /*
	     * Forwarding to regulators jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "regulators", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView regulators(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("regulators");
  	    }
	    
	    /*
	     * Forwarding to contact jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "contact", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView contact(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("contact");
  	    }
	    
	    /*
	     * Forwarding to restrictedDistributor jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "restrictedDistributor", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView restrictedDistributor(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("restrictedDistributor");
  	    }
	    
	    /*
	     * Forwarding to sourceIncome jsp page
	     * @ sarthak
	     */
	    @RequestMapping(value = "sourceIncome", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView sourceIncome(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("sourceIncome");
  	    }
	    
	    @RequestMapping(value = "stocksDistribution", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView stocksDistribution(@RequestParam("custId") String custId, ModelMap m){
	    	 m.addAttribute("custId", custId);
  	    	 return new ModelAndView("stocksDistribution");
  	    }
	    //1st change -end   

	    @RequestMapping(value = "deleteByCustomerId", method = RequestMethod.POST)
		// @ResponseBody
	    public ModelAndView deleteByCustomerId(@RequestBody CustomerDocument y, ModelMap m){
	    	usersService.deleteByCustomerId(y);
	    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
		    LoginUser loginUser = usersService.getUser(name);
			m.addAttribute("uid", loginUser.getId());
		    m.addAttribute("uname", loginUser.getFirstname());
		    m.addAttribute("urname", loginUser.getRole().getName());
	    	return new ModelAndView("success-login");
	    }
	    
	    @RequestMapping(value = "getAdminDocRules", method = RequestMethod.GET)
	    @ResponseBody
	    public List<DocumentDto> getAdminDocRules(){
	        return usersService.getAdminDocRules();
	    }
	    
	    @RequestMapping(value = "deleteAdminByDocumentId", method = RequestMethod.POST)
		// @ResponseBody
	    public ModelAndView deleteAdminByDocumentId(@RequestBody Document y, ModelMap m){
	    	usersService.deleteAdminByDocumentId(y);
	    	String name = SecurityContextHolder.getContext().getAuthentication().getName();
		    LoginUser loginUser = usersService.getUser(name);
			m.addAttribute("uid", loginUser.getId());
		    m.addAttribute("uname", loginUser.getFirstname());
		    m.addAttribute("urname", loginUser.getRole().getName());
	    	return new ModelAndView("success-login");
	    }
	    
	    @RequestMapping(value = "saveAllUsers", method = RequestMethod.POST)
	    //@ResponseBody
	    public ModelAndView saveAllUsers(@RequestBody List<LoginUser> y){
	    	usersService.saveAllUsers(y);
	    	return new ModelAndView("newTables");
	    }
		
		@RequestMapping(value = "downloadMyFile", method = RequestMethod.GET)
  		// @ResponseBody
  		public ModelAndView downloadMyFile(@RequestParam("filePath") String filePath, @RequestParam("fileName") String fileName, ModelMap m){
	    	 if(filePath!=null && fileName!=null){
	    		 String filePathTemp = filePath;
		    	 String destfilepath = filePathTemp.replaceAll("\\\\","/");
	    		 m.addAttribute("destFilepath", destfilepath);
	    		 m.addAttribute("destFileName", fileName);
	    	 }
  	    	 return new ModelAndView("downLoadFile");
  	    }
		
	    @RequestMapping(value = "downloadFileStatus", method = RequestMethod.GET)
  		// @ResponseBody
	    public ModelAndView downloadFileStatus(@RequestParam("filePath") String filePath, @RequestParam("fileName") String fileName, ModelMap m){
	    	 if(filePath!=null && fileName!=null){
	    		 String filePathTemp = filePath;
		    	 String destfilepath = filePathTemp.replaceAll("\\\\","/");
	    		 m.addAttribute("destFilepath", destfilepath);
	    		 m.addAttribute("destFileName", fileName);
	    	 }
  	    	 return new ModelAndView("downLoadFile");
  	    }
	    
	    @RequestMapping(value = "getAllAuditor", method = RequestMethod.GET)
	    @ResponseBody
	    public List<CustomerDetails> getAllAuditor() {
	        return usersService.getAllAuditor();
	    }
	    
	    @RequestMapping(value = "exportMakerCheckerDetails", method = RequestMethod.GET)
	    public String exportMakerCheckerDetails(ModelMap model) throws SQLException {
	    	List<CustomerDetails> listofCustomer = usersService.getAllCustomers();
			XSSFWorkbook workbook = new XSSFWorkbook(); 
		    XSSFSheet sheet = workbook.createSheet("Customer Data");
		    Map<String, Object[]> data = new HashMap<String, Object[]>();
		    data.put("1", new Object[] {"ID", "NAME_OF_CUSTOMER_ENTITY", "STATUS", "FATCA_STATUS","MORE_INFO" });
		    int i=1;
		    for(CustomerDetails custDeta : listofCustomer){
		    		i++;
		    		data.put( i +"", new Object[] {custDeta.getId(), custDeta.getNameOfEntityCustomer(), custDeta.getStatus(),custDeta.getFatcaStatus(), custDeta.getMoreInfo()});
		    }
		    Set<String> keyset = data.keySet();
		    int rownum = 0;
		    for (String key : keyset){
		        Row row = sheet.createRow(rownum++);
		        Object [] objArr = data.get(key);
		        int cellnum = 0;
		        for (Object obj : objArr){
		           Cell cell = row.createCell(cellnum++);
		           if(obj instanceof String)
		                cell.setCellValue((String)obj);
		            else if(obj instanceof Long)
		                cell.setCellValue((Long)obj);
		        }
		    }
		    try{
		    	InputStream inputStream = getClass().getClassLoader().getResourceAsStream("/properties/export.properties");
				Properties prop = new Properties();
				prop.load(inputStream);
				if(inputStream == null){
					throw new FileNotFoundException("property file '" + inputStream +"' not found in the classpath." ); 
				}
				String filePathExportStr = prop.getProperty("filePathExportProp");
				String  filePathExportStrTemp = filePathExportStr.replaceAll("\\\\","/");
		        File file = new File(filePathExportStrTemp.toString());
				FileOutputStream out = new FileOutputStream(file);
			    workbook.write(out);
			    out.close();	
			    String filePath = prop.getProperty("filePathExportProp");
		        String  fileName = prop.getProperty("fileNameExportProp");
		        if(filePath!=null && fileName!=null){
		       	 	String  filePathTemp = filePath;
		       	 	String  destfilepath = filePathTemp.replaceAll("\\\\","/");
		            model.addAttribute("destFilepath", destfilepath);
		            model.addAttribute("destFileName", fileName);
		        }
		    }catch (Exception e){
		        e.printStackTrace();
		    }
	        return "exportMakerCheckerJspDetails";
	    } 
}
